BigQueryで削除後のテーブルと同名テーブルを作成した場合も、タイムトラベル可能か調べてみた
クラスメソッド株式会社データアナリティクス事業本部所属のニューシロです。
今回はGoogle CloudのサービスであるBigQueryで、削除後のテーブルと同名テーブルを作成した場合もタイムトラベルが使えるか調べてみました。
タイムトラベルとは
デフォルトでは、過去 7 日間のタイムトラベル期間内であれば、どの時点のデータにもアクセスできます。タイムトラベルを使用すると、更新されたデータや削除されたデータのクエリを実行すること、削除されたテーブルや期限切れのテーブルを復元できます。
DevelopersIOでも、このタイムトラベルについて検証した記事があります。
以下の2つの記事で勉強させていただきました、ありがとうございます。
この機能により、誤ってデータを消してしまった場合や、テーブルを削除してしまった場合にもデータを復元することができます。
しかし、どんな状況でも本当に復元できるのか気になります。特に削除後に同名テーブルを作り直した場合、過去のデータも上書きされたりしないのかな、と気になってしまいました。あらかじめ手を動かして調べておくことで、万が一の際にも備えられると思い今回この記事を書いてみました。
タイムトラベルの検証
準備
以下のSQLでデータセットDevelopersIO下にテーブルtime_travel_01をあらかじめ作成しておきました。後の作業でも利用するため、テーブルtime_travel_02、time_travel_03、time_travel_04も同スキーマで作成し、同データをINSERTしておきます。
CREATE TABLE DevelopersIO.time_travel_01 (
id INT64,
name STRING
);
INSERT DevelopersIO.time_travel_01 (id, name)
VALUES(1, 'apple'),
(2, 'banana'),
(3, 'orange');
既存データを削除した場合のタイムトラベル
データを削除してみます。
DELETE FROM DevelopersIO.time_travel_01 WHERE id = 3;
データを確認します。
SELECT * FROM DevelopersIO.time_travel_01 ORDER BY id;
結果
id = 3のデータが消えていますが、SQLを用いてタイムトラベルでアクセスしてみます。
SELECT * FROM DevelopersIO.time_travel_01 FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
結果
消してしまったid = 3のデータを見ることができました！
既存テーブルを削除した場合のタイムトラベル
テーブルを削除した場合もタイムトラベルが使えます。
検証のため、DROP文でこのテーブルを削除します。
DROP TABLE DevelopersIO.time_travel_01;
削除後は先ほど行ったSQLを用いてタイムトラベルする方法が使えません。
この場合は、bqコマンドでのタイムトラベルを使うことで、テーブルを復元することができます。
ドキュメント通り、1時間前のテーブルをtime_travel_01_copyという名前で復元してみましょう。
bq cp DevelopersIO.time_travel_01@-3600000 DevelopersIO.time_travel_01_copy
このコマンドでtime_travel_01_copyというテーブルが作成できたので、データを確認するためにSQLを実行します。
SELECT * FROM DevelopersIO.time_travel_01_copy ORDER BY id;
結果
復元できています。
このように、テーブルを削除した場合もデータを復元できるタイムトラベルは非常に便利な機能です。
本題
削除したテーブルと同名のテーブルを作り直した場合のタイムトラベル可否
本題です。先ほど検証したテーブルと同スキーマ、同データがINSERTされたテーブルtime_travel_02を利用します。このテーブルを削除し、その後同名テーブルを作り直します。スキーマ定義のみで、データは入れません。
DROP TABLE DevelopersIO.time_travel_02; CREATE TABLE DevelopersIO.time_travel_02 ( id INT64, name STRING );
SQLでタイムトラベルを行います。
SELECT * FROM DevelopersIO.time_travel_02 FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
この場合、SQLでのタイムトラベルはできませんでした。
ログによると、対象のテーブルが見つからない、とのことでした。同名テーブルでも別のテーブル扱いのようです。
しかしながら、bqコマンドでのタイムトラベルは成功しました。
bq cp DevelopersIO.time_travel_02@-3600000 DevelopersIO.time_travel_02_copy
ログ上では成功したので、このbqコマンドで作り直したテーブルについて、SQLでデータを確認します。
SELECT * FROM DevelopersIO.time_travel_02_copy ORDER BY id;
結果
タイムトラベルでテーブルの再作成はできているようです。一安心です。
CREATE OR REPLACE文でテーブルを作り直した場合のタイムトラベル可否
念の為他のパターンも調査してみます。CREATE OR REPLACE TABLEで、既存テーブルを作り直し、タイムトラベルをしてみましょう。
使用するテーブルはtime_travel_03です。格納されているデータはこれまでと同じです。
CREATE OR REPLACE TABLE DevelopersIO.time_travel_03 ( id INT64, name STRING );
まずはSQLでのタイムトラベル。
SELECT * FROM DevelopersIO.time_travel_03 FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
結果
SQLでのタイムトラベルに成功しました。
前述のように、テーブルを削除して再作成だとエラーが起きたので、ここは失敗すると予想していました。もちろん、bqコマンドでもタイムトラベル可能でした。
TRUNCATE文でデータを削除した場合のタイムトラベル可否
こちらも気になったのでやってみました。おそらくできるだろうな、と思いつつも、手を動かしてみました。
使用するテーブルはtime_travel_04です。格納されているデータはこれまでと同じです。
TRUNCATE TABLE DevelopersIO.time_travel_04;
テーブル内のデータを消去できたので、SQLでタイムトラベルしてみます。
SELECT * FROM DevelopersIO.time_travel_04 FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
予想通り、TRUNCATE文で削除した場合もSQLでタイムトラベルできています。bqコマンドでもタイムトラベル可能でした。
終わりに
万が一のためにも、タイムトラベルの機能について把握しておきたかったので、試してよかったです。これからも手を動かして、扱う技術について気になったことは積極的に調べていきたいです。
引用・参照まとめ
- タイムトラベルとフェイルセーフによるデータ保持 | BigQuery | Google Cloud - タイムトラベル
- タイムトラベルで BigQuery の削除済みデータにアクセス＆復元してみた | DevelopersIO
- BigQueryのタイムトラベルでのテーブル復元方法について考えてみた | DevelopersIO
- テーブルの管理 | BigQuery | Google Cloud - 削除されたテーブルを復元する
- Data definition language (DDL) statements in GoogleSQL | BigQuery | Google Cloud - Creating or replacing a table
- Data manipulation language (DML) statements in GoogleSQL | BigQuery | Google Cloud - TRUNCATE TABLE statement
